package com.wfs.myapp.repository.bk;

import com.wfs.myapp.entity.domain.bk.BkOrder;
import com.wfs.myapp.entity.vo.OrderVo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface OrderRepository extends JpaRepository<BkOrder, Long> {

    List<BkOrder> findByBookId(Long bookId);
    List<BkOrder> findByBookIdAndStatus(Long bookId, Integer status);

    @Query(nativeQuery = true, value = " SELECT bu.nickname borrowUserName, bu.openid borrowOpenid " +
            " FROM bk_order o " +
            " LEFT JOIN user bu ON o.borrow_user_id = bu.id " +
            " WHERE o.status = :status" +
            " ORDER BY o.created_time desc" +
            " LIMIT 0,:count "
    )
    List<OrderVo> findByStatus(Integer status, Integer count);

    @Query(nativeQuery = true, value = " SELECT count(*) " +
            " FROM bk_order o " +
            " LEFT JOIN user mu ON o.master_user_id = mu.id " +
            " LEFT JOIN user bu ON o.borrow_user_id = bu.id " +
            " LEFT JOIN bk_book b ON o.book_id = b.id " +
            " WHERE 1=1 " +
            " AND IF (:direction = 0, o.borrow_user_id = :userId, o.master_user_id = :userId) " +
            " AND IF (COALESCE(:statusList) is not null, o.status in (:statusList), 1=1) ")
    long countByMasterUserIdAndStatus(Integer direction, Long userId, List<Integer> statusList);
    @Query(nativeQuery = true, value = " SELECT o.*, " +
            " mu.nickname masterUserName, mu.avatar masterUserAvatar, bu.nickname borrowUserName, bu.avatar borrowUserAvatar, b.book_name, b.book_avatar, b.author bookAuthor " +
            " FROM bk_order o " +
            " LEFT JOIN user mu ON o.master_user_id = mu.id " +
            " LEFT JOIN user bu ON o.borrow_user_id = bu.id " +
            " LEFT JOIN bk_book b ON o.book_id = b.id " +
            " WHERE 1=1 " +
            " AND IF (:direction = 0, o.borrow_user_id = :userId, o.master_user_id = :userId) " +
            " AND IF (COALESCE(:statusList) is not null, o.status in (:statusList), 1=1) " +
            " order by o.status asc, o.created_time desc " +
            " limit :page,:size ")
    List<OrderVo> findByMasterUserIdAndStatus(Integer direction, Long userId, List<Integer> statusList, Integer page, Integer size);

    @Query(nativeQuery = true, value = " SELECT o.*, " +
            " mu.nickname masterUserName, mu.avatar masterUserAvatar, bu.nickname borrowUserName, bu.avatar borrowUserAvatar, b.book_name, b.book_avatar, b.author bookAuthor " +
            " FROM bk_order o " +
            " LEFT JOIN user mu ON o.master_user_id = mu.id " +
            " LEFT JOIN user bu ON o.borrow_user_id = bu.id " +
            " LEFT JOIN bk_book b ON o.book_id = b.id " +
            " WHERE 1=1 " +
            " AND IF (:direction is not null, IF (:direction = 0, o.borrow_user_id = :userId, o.master_user_id = :userId), o.borrow_user_id = :userId OR o.master_user_id = :userId) " +
            " AND (mu.nickname like :name OR bu.nickname like :name OR b.book_name like :name OR b.author like :name) " +
            " order by o.status asc, o.created_time desc "
            )
    List<OrderVo> searchByName(Long userId, Integer direction, String name);

    @Query(nativeQuery = true, value = " SELECT o.*, bu.nickname borrowUserName, bu.openid borrowOpenid, b.book_name bookName  " +
            " FROM bk_order o " +
            " LEFT JOIN user bu ON o.borrow_user_id = bu.id " +
            " LEFT JOIN bk_book b ON o.book_id = b.id " +
            " WHERE o.id in :orderIdList " +
            " AND o.status in :statusList " +
            " ORDER BY o.created_time asc "
    )
    List<OrderVo> findAllByIds(List<Long> orderIdList, List<Integer> statusList);
}




